How to rebuild the Content Studio full-text index

[This is preliminary documentation and is subject to change.]

Author: Halfvares Mats, Teknikhuset AB.

Published: 2008-10-10

Applies to:

Content Studio ver. 5

Type: How to

More information

Sometimes it is neccessary to rebuild the Content Studio full text indexes. This i always needed when you have restored a Content Studio site database on another database server or on the same server but with another name. The reason for this is that the name of the full text catalogs containing the index is given a name that is dependent on the SQL Server ID of the database ie. "ftxt_DATABASEID_CS_ContentBinary" and "ftxt_DATABASEID_XML_Data" where DATABASEID is the internal id of the database. This ID is not preserved when you restore the database as a new database but the full-text information will remain. You also will note that the database reports that the full text catalog has been lost. In this situation you will need to delete the 2 catalogs and completly rebuild them.

Starting with Content Studio 5.2 SQL Server 2000 or earlier no longer are supported with the product. Version 5.2 uses features that are not available in earlier versions of SQL Server. One of these is the varbinary(max) datatype that makes it possible to support automatic background fulltext indexing. With this datatype, documents can be fulltext indexed within a few seconds and fulltext indexing jobs used in earlier versions no longer are needed. The second script does not support this and will turn off automatic indexing if run on a version 5.2 database.

To rebuild the catalogs

Start the SQL Query analyser tool from the tools menu in Enterprise Manager or open a new query window in Management Studio and paste in the script that rebuilds the catalogs. Below is the TSQL script you can use to rebuild the catalogs.

Important
There are two versions of the script, choose the correct one.

Script to use in version 5.2 and later

CopySql: version 5.2 and later

-- Drop all fulltext index catalogs on the current database 
-- if they have the wrong name
DECLARE @script nvarchar(4000);

--Drop all fulltext indexes if they exists
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
  BEGIN
    EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
  END
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
  BEGIN
    EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
  END

DECLARE @catalog sysname;
DECLARE Curs CURSOR FAST_FORWARD READ_ONLY  FOR
        SELECT [name] FROM dbo.sysfulltextcatalogs 
OPEN Curs;
FETCH NEXT FROM Curs INTO @catalog
WHILE @@FETCH_STATUS = 0
  BEGIN
      SELECT @script = N'DROP FULLTEXT CATALOG [' + @Catalog + ']'
      EXECUTE sp_executesql @script;
      FETCH NEXT FROM Curs INTO @catalog
  END
CLOSE Curs;
DEALLOCATE Curs;
GO
--The content fulltext column
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
    SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']'
    EXECUTE sp_executesql @script;
END
--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
  BEGIN
    EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
  END
GO
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';

    SELECT @script = N'
    CREATE FULLTEXT INDEX ON [dbo].[tbl_modules_content]
    (
      [ContentBinary] TYPE COLUMN [Fil_Ext] LANGUAGE ''Swedish'',
      [introduction] LANGUAGE ''Swedish'',
      [Keywords] LANGUAGE ''Swedish''
    )
    KEY INDEX [PK_tbl_modules_content] ON [' + @Catalog + '] WITH CHANGE_TRACKING AUTO;';
EXECUTE sp_executesql @script;
SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_modules_content] ENABLE;';
EXECUTE sp_executesql @script;
GO
--The Xml-index fulltext column
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = N'ftxt_' + CAST(DB_ID(DB_NAME()) AS nvarchar(10)) + N'_XML_Data'

IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
    SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']' 
    EXECUTE sp_executesql @script;
END
--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
  BEGIN
    EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
  END
GO
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_XML_Data';
        SELECT @script = N'
        CREATE FULLTEXT INDEX ON [dbo].[tbl_xml_data]
        (
          [FullData] LANGUAGE ''Swedish''
        )
        KEY INDEX [PK_tbl_xml_data] ON [' + @Catalog + '] WITH CHANGE_TRACKING AUTO;';
EXECUTE sp_executesql @script;
SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_xml_data] ENABLE;';
EXECUTE sp_executesql @script;

Script to in version 5.1 and 5.0

CopySql: version 5.1 and 5.0

-- Drop all CS fulltext index catalogs on the current database 
-- if they have the wrong name
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @script nvarchar(4000);

--Drop all fulltext indexes if they exists
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
  END
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
  END

DECLARE @catalog sysname;
DECLARE Curs CURSOR FAST_FORWARD READ_ONLY  FOR
        SELECT [name] FROM dbo.sysfulltextcatalogs 
OPEN Curs;
FETCH NEXT FROM Curs INTO @catalog
WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @Ver = 2005
        SELECT @script = N'DROP FULLTEXT CATALOG [' + @Catalog + ']'

    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''drop'';';

    EXECUTE sp_executesql @script;
    FETCH NEXT FROM Curs INTO @catalog
  END
CLOSE Curs;
DEALLOCATE Curs;

GO
--The content fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';

IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
    IF @Ver = 2005
        SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']'

    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';

    EXECUTE sp_executesql @script;
END

--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
  END

GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';

IF @Ver = 2005
    BEGIN
        SELECT @script = N'
        CREATE FULLTEXT INDEX ON [dbo].[tbl_modules_content]
        (
          [ContentBinary] TYPE COLUMN [Fil_Ext] LANGUAGE ''Swedish'',
          [introduction] LANGUAGE ''Swedish'',
          [Keywords] LANGUAGE ''Swedish''
        )
        KEY INDEX [PK_tbl_modules_content] ON [' + @Catalog + '] WITH CHANGE_TRACKING MANUAL;';
        EXECUTE sp_executesql @script;
        SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_modules_content] ENABLE;';
        EXECUTE sp_executesql @script;
    END
ELSE
    BEGIN
        EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'create', @Catalog, N'PK_tbl_modules_content'
        EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'introduction', N'add', 0
        EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'Keywords', N'add', 0
        EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'ContentBinary', N'add', 0, N'Fil_ext'
        EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'activate';
    END

GO

--The Xml-index fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = N'ftxt_' + CAST(DB_ID(DB_NAME()) AS nvarchar(10)) + N'_XML_Data'

IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
    IF @Ver = 2005
        SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']' /*+
                         N'IN PATH ''E:\SQL Server 2005\FTDATA'';';*/
    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';

    EXECUTE sp_executesql @script;
END

--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
  END
GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_XML_Data';

IF @Ver = 2005
    BEGIN
        SELECT @script = N'
        CREATE FULLTEXT INDEX ON [dbo].[tbl_xml_data]
        (
          [FullData] LANGUAGE ''Swedish''
        )
        KEY INDEX [PK_tbl_xml_data] ON [' + @Catalog + '] WITH CHANGE_TRACKING AUTO;';
        EXECUTE sp_executesql @script;
        SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_xml_data] ENABLE;';
        EXECUTE sp_executesql @script;
    END
ELSE
    BEGIN
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'create', @Catalog,  N'PK_tbl_xml_data';
        EXECUTE sp_fulltext_column N'[dbo].[tbl_xml_data]', N'FullData', N'add', 0;
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'activate';
        --start background index update
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_change_tracking';
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_background_updateindex';
    END

Execute the script and close Management Studio or Query Analyzer.

You should now be able to work with the Content Studio full-text index as usual.